﻿/*功能描述：绩效设置
作者：李周蓬
时间：2021-12-13
修改人：李周蓬
修改时间：2021-12-13*/
using Dapper;
using HongStone.Domain.PerformanceModel;
using HongStone.Dto;
using HongStone.Dto.PerformanceDto;
using HongStone.Ince;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HongStone.Repository
{
    public class PerformanceDal : IPerformance
    {
        private readonly HongStoneContext _hongStoneContext;
        private readonly IConfiguration _configuration;
        public PerformanceDal(HongStoneContext hongStoneContext, IConfiguration configuration)
        {
            _hongStoneContext = hongStoneContext;
            _configuration = configuration;
        }

        /// <summary>
        /// 显示考核任务
        /// </summary>
        /// <returns></returns>
        public ResultData ShowTheAssessmentTask()
        {
            var list = _hongStoneContext.theAssessmentTasks.AsQueryable();
            return new ResultData() { Code = 1, Data = list };
        }
        /// <summary>
        /// 显示考核统计
        /// </summary>
        /// <returns></returns>
        public ResultData ShowAssessmentTotal()
        {
            var list = _hongStoneContext.assessmentTotals.AsQueryable();
            return new ResultData() { Code = 1, Data = list };
        }
        /// <summary>
        /// 添加考核任务
        /// </summary>
        /// <returns></returns>
        public ResultData AddTheAssessmentTask(TheAssessmentTask theAssessmentTask)
        {
            _hongStoneContext.theAssessmentTasks.Add(theAssessmentTask);
            return new ResultData() { Code = _hongStoneContext.SaveChanges() };
        }
        /// <summary>
        /// 修改考核任务
        /// </summary>
        /// <param name="theAssessmentTask"></param>
        /// <returns></returns>
        public ResultData UpdateTheAssessmentTask(TheAssessmentTask theAssessmentTask)
        {
            _hongStoneContext.Entry(theAssessmentTask).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
            return new ResultData() { Code = _hongStoneContext.SaveChanges() };
        }
        /// <summary>
        /// 删除考核任务
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ResultData DelTheAssessmentTask(string id)
        {
            var kk = _hongStoneContext.theAssessmentTasks.Where(m => m.ItemNumber == id).FirstOrDefault();
            _hongStoneContext.theAssessmentTasks.Remove(kk);
            return new ResultData() { Code = _hongStoneContext.SaveChanges() };
        }
        /// <summary>
        /// 归档
        /// </summary>
        /// <returns></returns>
        public ResultData UpdateGuiDang(string id)
        {
            var obj = _hongStoneContext.theAssessmentTasks.FirstOrDefault(m => m.ItemNumber == id);
            if (obj != null)
            {
                obj.Pigeonhole = 1;
            }
            return new ResultData() { Code = _hongStoneContext.SaveChanges() };
        }
        /// <summary>
        /// 显示部门内考核
        /// </summary>
        /// <param name="itemNumber"></param>
        /// <returns></returns>
        public ResultData ShowDeptAssessment(string itemNumber)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"select a.*,b.DeptName from DeptAssessment a join Department b on a.DeptId=b.DeptId where 1=1 ";
                if (!string.IsNullOrWhiteSpace(itemNumber))
                {
                    sql += $" and a.ItemNumber='{itemNumber}'";
                }
                var kk = conn.Query<ShowDeptAssessmentDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = kk };
            }
        }
        /// <summary>
        /// 提交考核
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ResultData UpdateKaoHe(string id)
        {
            var obj = _hongStoneContext.deptAssessments.FirstOrDefault(m => m.DeptAssessmentId == id);
            if (obj != null)
            {
                obj.State = 1;
            }
            return new ResultData() { Code = _hongStoneContext.SaveChanges() };
        }
        /// <summary>
        /// 重新提交考核
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ResultData UpdateChongKaoHe(string id)
        {
            var obj = _hongStoneContext.deptAssessments.FirstOrDefault(m => m.DeptAssessmentId == id);
            if (obj != null)
            {
                obj.State = 0;
            }
            return new ResultData() { Code = _hongStoneContext.SaveChanges() };
        }
        /// <summary>
        /// 显示考核项目
        /// </summary>
        /// <param name="name"></param>
        /// <param name="year"></param>
        /// <param name="deptid"></param>
        /// <param name="itemNumber"></param>
        /// <returns></returns>
        public ResultData ShowProjectAssessment(string name, int year, int deptid, string itemNumber)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"select a.*,b.ProjectName,d.ItemNumber,e.TName,b.EnteringTime from ProjectAssessment a join Project b on a.ProjectId=b.ProjectId join TheAssessmentTask d on a.ItemNumber=d.ItemNumber join BudingType e on a.BuildingId = e.TId where 1=1 ";
                if (!string.IsNullOrWhiteSpace(itemNumber))
                {
                    sql += $" and a.ItemNumber='{itemNumber}'";
                }
                if (!string.IsNullOrWhiteSpace(name))
                {
                    sql += $" and a.AssessmentName like '%" + name + "%'";
                }
                if (deptid > 0)
                {
                    sql += $" and b.DeptId='{deptid}'";
                }
                if (year > 0)
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),EnteringTime),7,4)='{year}'";
                }
                var kk = conn.Query<ProjectAssessmentDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = kk };
            }
        }
        /// <summary>
        /// 绑定部门
        /// </summary>
        /// <returns></returns>
        public ResultData BindDept()
        {
            var list = _hongStoneContext.departments.ToList();
            return new ResultData() { Code = 1, Data = list };
        }
        /// <summary>
        /// 添加考核项目
        /// </summary>
        /// <returns></returns>
        public ResultData AddProjectAssessment(ProjectAssessment projectAssessment)
        {
            _hongStoneContext.projectAssessments.Add(projectAssessment);
            return new ResultData() { Code = _hongStoneContext.SaveChanges() };
        }
        /// <summary>
        /// 修改考核项目
        /// </summary>
        /// <param name="projectAssessment"></param>
        /// <returns></returns>
        public ResultData UpdateProjectAssessment(ProjectAssessment projectAssessment)
        {
            _hongStoneContext.Entry(projectAssessment).State = Microsoft.EntityFrameworkCore.EntityState.Modified;
            return new ResultData() { Code = _hongStoneContext.SaveChanges() };
        }
        /// <summary>
        /// 删除考核项目
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ResultData DelProjectAssessment(string id)
        {
            var kk = _hongStoneContext.projectAssessments.Where(m => m.ProjectAssessmentId == id).FirstOrDefault();
            _hongStoneContext.projectAssessments.Remove(kk);
            return new ResultData() { Code = _hongStoneContext.SaveChanges() };
        }
        /// <summary>
        /// 绑定建筑类型
        /// </summary>
        /// <returns></returns>
        public ResultData BindBudingType()
        {
            var list = _hongStoneContext.budingTypes.AsQueryable();
            return new ResultData() { Code = 1, Data = list };
        }
        /// <summary>
        /// 修改考核名称
        /// </summary>
        /// <param name="projectAssessment"></param>
        /// <returns></returns>
        public ResultData UpdateAssessmentName(ProjectAssessment projectAssessment)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"update ProjectAssessment set AssessmentName='{projectAssessment.AssessmentName}',JiXiao='{projectAssessment.JiXiao}',Remark='{projectAssessment.Remark}',Wan='{projectAssessment.Wan}',WeiWan='{projectAssessment.WeiWan}',LiWan='{projectAssessment.LiWan}',WanTotal='{projectAssessment.WanTotal}' where ProjectAssessmentId='{projectAssessment.ProjectAssessmentId}'";
                var kk = conn.Execute(sql);
                return new ResultData() { Code = kk };
            }
        }
        /// <summary>
        /// 修改考核分类
        /// </summary>
        /// <param name="projectAssessment"></param>
        /// <returns></returns>
        public ResultData UpdateAssessmentNames(ProjectAssessment projectAssessment)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"update ProjectAssessment set BuildingId='{projectAssessment.BuildingId}',GuiMo='{projectAssessment.GuiMo}',NanDu='{projectAssessment.NanDu}',XuNi='{projectAssessment.XuNi}' where ProjectAssessmentId='{projectAssessment.ProjectAssessmentId}'";
                var kk = conn.Execute(sql);
                return new ResultData() { Code = kk };
            }
        }
        /// <summary>
        /// 删除考核分类
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public ResultData DelAssessmentNames(string name)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"update ProjectAssessment set BuildingId=0,GuiMo=0,NanDu=0,XuNi=0 where ProjectAssessmentId='{name}'";
                var kk = conn.Execute(sql);
                return new ResultData() { Code = kk };
            }
        }
        /// <summary>
        /// 删除考核名称
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public ResultData DelAssessmentName(string name)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"update ProjectAssessment set  AssessmentName=null,JiXiao=0,Remark=null,Wan=0,WeiWan=0,LiWan=0 where ProjectAssessmentId='{name}'";
                var kk = conn.Execute(sql);
                return new ResultData() { Code = kk };
            }
        }
        /// <summary>
        /// 显示分页
        /// </summary>
        /// <param name="year"></param>
        /// <param name="deptid"></param>
        /// <param name="itemNumber"></param>
        /// <param name="totalcount"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public ResultData ShowKao(int year, int deptid, string itemNumber,out int totalcount,int pageindex=1,int pagesize=3)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"select a.*,b.ProjectName from ProjectAssessment a join Project b on a.ProjectId = b.ProjectId join Department c on b.DeptId=c.DeptId join TheAssessmentTask d on a.ItemNumber = d.ItemNumber join BudingType e on a.BuildingId = e.TId where 1 = 1 ";
                if (!string.IsNullOrWhiteSpace(itemNumber))
                {
                    sql += $" and a.ItemNumber='{itemNumber}'";
                }
                if (deptid > 0)
                {
                    sql += $" and b.DeptId='{deptid}'";
                }
                if (year > 0)
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),EnteringTime),7,4)='{year}'";
                }
                var kk = conn.Query<ShowKaoDto>(sql).ToList();
                totalcount = kk.Count();
                kk = kk.OrderBy(m => m.ProjectAssessmentId).Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();
                return new ResultData() { Code = 1, Data = kk,TotalCount=totalcount };
            }
        }
        /// <summary>
        /// 显示项目进度
        /// </summary>
        /// <returns></returns>
        public ResultData ShowAssessSchedule(string itemNumber,out int totalcount)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"select c.*,b.AssessmentName from TheAssessmentTask a join ProjectAssessment b on a.ItemNumber=b.ItemNumber join AssessSchedule c on b.ProjectAssessmentId=c.ProjectAssessmentId where 1=1 ";
                if (!string.IsNullOrWhiteSpace(itemNumber))
                {
                    sql += $" and a.ItemNumber='{itemNumber}'";
                }
                var list= conn.Query<ShowScheduleDto>(sql).ToList();
                totalcount = list.Count();
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        /// 发起审批
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public ResultData UpdateShenPi(string ids)
        {
            var kk = ids.Split(',');
            foreach (var item in kk)
            {
                var obj = _hongStoneContext.assessSchedules.FirstOrDefault(m => m.AssessScheduleId == item);
                if (obj != null)
                {
                    obj.State = 1;
                }
            }
            return new ResultData() { Code = _hongStoneContext.SaveChanges() };
        }
        /// <summary>
        /// 调整数量
        /// </summary>
        /// <returns></returns>
        public ResultData ShowTiao()
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"select count(0) kk from AssessSchedule where State=1";
                var list = conn.Query(sql).ToList();
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        /// 显示部门内奖金计算
        /// </summary>
        /// <param name="itemnumber"></param>
        /// <returns></returns>
        public ResultData ShowJi(string itemnumber)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"select *,(d.MonthAvg*d.BeiShu) as DeptCount,(d.MonthAvg*6) as Shang,(d.MonthAvg*6) as Xia from DeptAssessment a join Department b on a.DeptId=b.DeptId join DeptSalaryCountDept c on a.DeptId=c.DeptId join DeptSalaryCount d on c.DeptSalaryCountId=d.DeptSalaryCountId where 1=1  ";
                if (!string.IsNullOrWhiteSpace(itemnumber))
                {
                    sql += $" and a.ItemNumber='{itemnumber}'";
                }
                var list = conn.Query<ShowJiDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        /// 显示项目奖金调整
        /// </summary>
        /// <param name="itemnumber"></param>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public ResultData ShowTiaokk(string itemnumber,int deptid)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = "select a.*,b.StaffId,b.State,b.StaffName,c.DeptId,c.DeptName from SalaryUpdate a join Staff b on a.StaffId=b.StaffId join Department c on b.DeptId=c.DeptId join TheAssessmentTask d on a.ItemNumber=d.ItemNumber where 1=1  ";
                if (!string.IsNullOrWhiteSpace(itemnumber))
                {
                    sql += $" and a.ItemNumber='{itemnumber}'";
                }
                if (deptid > 0)
                {
                    sql += $" and b.DeptId='{deptid}'";
                }
                var list = conn.Query<SalaryTiaoDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = list };
            }
               
        }
        /// <summary>
        /// 部门内奖金计算
        /// </summary>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public ResultData ShowSalaryCount(string itemnumber)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"select * from Department a join DeptAssessment b on a.DeptId=b.DeptId where 1=1 ";
                if (!string.IsNullOrWhiteSpace(itemnumber))
                {
                    sql += $" and b.ItemNumber='{itemnumber}'";
                }
                var list = conn.Query<DeptCountDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        ///部门内奖金计算1
        /// </summary>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public ResultData ShowStaffSalaryCount(int deptid)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"select b.*,a.StaffName,a.DeptId,(b.Salary*b.WorkTime+b.Yuan+b.Yu) as He,(b.Salary*b.WorkTime+b.Yuan) as Hes,((b.Salary*b.WorkTime+b.Yuan+b.Yu)*b.Bei) as Xia,((b.Salary*b.WorkTime+b.Yuan+b.Yu)*b.Bei) as Shang,((b.Salary*b.WorkTime+b.Yuan+b.Yu)*b.Bei-(b.Salary*b.WorkTime+b.Yuan+b.Yu)) as Cha,((b.Salary*b.WorkTime+b.Yuan+b.Yu)*b.Bei-(b.Salary*b.WorkTime+b.Yuan+b.Yu)) as Chas  from Staff a join DeptCount b on a.StaffId=b.StaffId join Department c on a.DeptId=c.DeptId where 1=1  ";
                if (deptid>0)
                {
                    sql += $" and a.DeptId='{deptid}'";
                }
                var list = conn.Query<DeptSalaryCountDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        /// 分配详情
        /// </summary>
        /// <param name="itemnumber"></param>
        /// <param name="deptid"></param>
        /// <returns></returns>
        public ResultData ShowTiaoXiang(string itemnumber, int deptid)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"  select a.DeptName,a.DeptId,b.State,b.ItemNumber from Department a left join DeptAssessment b on a.DeptId=b.DeptId left join SalaryUpdate c on b.ItemNumber=c.ItemNumber group by a.DeptName,a.DeptId,b.State,b.ItemNumber having 1=1  ";
                if (!string.IsNullOrWhiteSpace(itemnumber))
                {
                    sql += $" and b.ItemNumber='{itemnumber}'";
                }
                if (deptid > 0)
                {
                    sql += $" and b.DeptId='{deptid}'";
                }
                var list = conn.Query<ShowTiaoXiangDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        /// 年度总奖金分配显示
        /// </summary>
        /// <param name="deptid"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public ResultData ShowYearBonus(int deptid,int state)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"select *,(d.DeptTiao+d.GongSiTiao+d.ProjectSum+d.DeptSum+d.ProjectSalary+d.DeptSalary+d.ProjectManager+d.YouYuan+d.Tu+d.BIM+d.RegistSalary+d.Bu+Yu) as HeJi,(d.DeptTiao+d.GongSiTiao+d.ProjectSum+d.DeptSum+d.ProjectSalary+d.DeptSalary+d.ProjectManager+d.YouYuan+d.Tu+d.BIM+d.RegistSalary) as HeJis,a.State as State from Staff a join Department b on a.DeptId=b.DeptId join DeptAssessment c on b.DeptId=c.DeptId join YearBonus d on a.StaffId=d.StaffId where 1=1 ";
                if (deptid > 0)
                {
                    sql += $" and b.DeptId='{deptid}'";
                }
                if (state>-1)
                {
                    sql += $" and a.State='{state}'";
                }
                var list = conn.Query<YearBonusDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        /// 查看考核名称
        /// </summary>
        /// <param name="staffid"></param>
        /// <returns></returns>
        public ResultData ShowChaSalaryUpdate(int staffid)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"select b.ProjectId,b.ProjectName,a.AssessmentName,c.DeptId,c.DeptName from ProjectAssessment a join Project b on a.ProjectId=b.ProjectId join Department c on b.DeptId=c.DeptId join Staff d on c.DeptId=d.DeptId where 1=1 ";
                if(staffid>0)
                {
                    sql += $" and d.StaffId='{staffid}'";
                }
                var list = conn.Query<ShowChaSalaryUpdateDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = list };
            }
        }
    }
}
